﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using YiYiTong.Rocket.Helper;

namespace YiYiTong.Rocket.DataAccess
{
    public class RoleDataAccess
    {
        #region 单例模式
        private RoleDataAccess()
        {
        }

        private static RoleDataAccess _instance;
        private static readonly object _syncObject = new object();

        public static RoleDataAccess Instance
        {
            get
            {
                if (_instance == null)
                {
                    lock (_syncObject)
                    {
                        if (_instance == null)
                        {
                            _instance = new RoleDataAccess();
                        }
                    }
                }
                return _instance;
            }
        }
        #endregion 单例模式

        /// <summary>
        /// 添加角色
        /// </summary>
        /// <param name="roleCode">角色代码</param>
        /// <param name="roleName">角色名称</param>
        /// <param name="createTime">创建时间</param>
        /// <param name="isDelete">删除标志位</param>
        /// <param name="remark">备注</param>
        /// <returns></returns>
        public int AddRole(string roleCode, string roleName, string createTime, int isDelete, string remark)
        {
            int ret = 0;
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = @"insert into Role (RoleCode,RoleName,CreateTime,IsDelete,Remark) 
                    values (@RoleCode,@RoleName,@CreateTime,@IsDelete,@Remark)";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@RoleCode", DbType.String, roleCode);
                db.AddInParameter(Procdbcomm, "@RoleName", DbType.String, roleName);
                db.AddInParameter(Procdbcomm, "@CreateTime", DbType.String, createTime);
                db.AddInParameter(Procdbcomm, "@IsDelete", DbType.Int32, isDelete);
                db.AddInParameter(Procdbcomm, "@Remark", DbType.String, remark);
                ret = db.ExecuteNonQuery(Procdbcomm);
            }
            catch (Exception ex)
            {
                ret = -99;
                //记录日志
                LogHelper.Error("添加角色-AddRole" + ex.ToString());
            }
            return ret;
        }


        /// <summary>
        /// 修改角色
        /// </summary>
        /// <param name="roleId">角色编号</param>
        /// <param name="roleCode">角色代码</param>
        /// <param name="remark">备注</param>
        /// <returns></returns>
        public int EditRole(int roleId, string roleCode, string remark)
        {
            int ret = 0;
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = "update Role set RoleCode =@RoleCode,Remark =@Remark where RoleID=@RoleID";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@RoleCode", DbType.String, roleCode);
                db.AddInParameter(Procdbcomm, "@Remark", DbType.String, remark);
                db.AddInParameter(Procdbcomm, "@RoleID", DbType.Int32, roleId);
                ret = db.ExecuteNonQuery(Procdbcomm);
            }
            catch (Exception ex)
            {
                //记录日志
                ret = -99;
                LogHelper.Error("修改角色-EditRole" + ex.ToString());
            }
            return ret;
        }

        /// <summary>
        /// 修改角色
        /// </summary>
        /// <param name="roleId">角色编号</param>
        /// <param name="roleCode">角色代码</param>
        /// <param name="roleName">角色名称</param>
        /// <param name="remark">备注</param> 
        /// <returns></returns>
        public int EditRole(int roleId, string roleCode, string roleName, string remark)
        {
            int ret = 0;
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = "update Role set RoleCode =@RoleCode,RoleName=@RoleName,Remark =@Remark where RoleID=@RoleID";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@RoleCode", DbType.String, roleCode);
                db.AddInParameter(Procdbcomm, "@RoleName", DbType.String, roleName);
                db.AddInParameter(Procdbcomm, "@Remark", DbType.String, remark);
                db.AddInParameter(Procdbcomm, "@RoleID", DbType.Int32, roleId);
                ret = db.ExecuteNonQuery(Procdbcomm);
            }
            catch (Exception ex)
            {
                //记录日志
                ret = -99;
                LogHelper.Error("修改角色-EditRole" + ex.ToString());
            }
            return ret;
        }

        /// <summary>
        /// 删除角色(逻辑删除)
        /// </summary>
        /// <param name="roleId"></param>
        /// <returns></returns>
        public int DelRole(int roleId)
        {
            int ret = 0;
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = "update Role set IsDelete = 1 where RoleID=@RoleID";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@RoleID", DbType.Int32, roleId);
                ret = db.ExecuteNonQuery(Procdbcomm);
            }
            catch (Exception ex)
            {
                //记录日志
                ret = -99;
                LogHelper.Error("删除角色-DelRole" + ex.ToString());
            }
            return ret;
        }

        /// <summary>
        /// 根据筛选条件查询角色
        /// </summary>
        /// <param name="roleCode">角色代码</param>
        /// <param name="roleName">角色名称</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageRows">每页多少行</param>
        /// <returns>返回角色列表，为空返回null</returns>
        public DataSet GetPageList(string roleCode, string roleName, int pageIndex, int pageRows)
        {
            DataSet ds = new DataSet();
            try
            {
                DBHelper db = DBFactory.AdminDB;
                DbCommand Procdbcomm = db.GetStoredProcCommand("proc_role_getlist");
                db.AddInParameter(Procdbcomm, "@roleCode", DbType.String, roleCode);
                db.AddInParameter(Procdbcomm, "@roleName", DbType.String, roleName);
                db.AddInParameter(Procdbcomm, "@pageIndex", DbType.Int32, pageIndex);
                db.AddInParameter(Procdbcomm, "@pageRows", DbType.Int32, pageRows);
                ds = db.ExecuteDataSet(Procdbcomm);
            }
            catch (Exception ex)
            {
                LogHelper.Error("根据筛选条件查询角色-GetPageList" + ex.ToString());
            }
            return ds;
        }

        /// <summary>
        /// 获取用户角色
        /// </summary>
        /// <param name="roleId"></param>
        /// <returns></returns>
        public DataTable GetUserRole(int roleId)
        {
            DataTable dtRole = new DataTable();
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = "select * from UserRole where RoleID=@RoleID";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@RoleID", DbType.Int32, roleId);
                var ds = db.ExecuteDataSet(Procdbcomm);
                if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count != 0)
                {
                    dtRole = ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                //记录日志
                LogHelper.Error("获取用户角色-GetUserRole" + ex.ToString());
            }
            return dtRole;
        }

        /// <summary>
        /// 根据角色编号获取角色信息
        /// </summary>
        /// <param name="roleId">角色编号</param>
        /// <returns></returns>
        public DataTable GetRoleInfo(int roleId)
        {
            DataTable dtRole = new DataTable();

            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = "select * from Role where IsDelete=0 and RoleID=@RoleID";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@RoleID", DbType.Int32, roleId);
                var ds = db.ExecuteDataSet(Procdbcomm);
                if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count != 0)
                {
                    dtRole = ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                //记录日志
                LogHelper.Error("根据角色编号获取角色信息-GetRoleInfo" + ex.ToString());
            }
            return dtRole;
        }

        /// <summary>
        /// 获取所有角色
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllRoleList()
        {
            DataTable dtRole = new DataTable();
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = "select * from Role where IsDelete=0";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                var ds = db.ExecuteDataSet(Procdbcomm);
                if (ds != null && ds.Tables[0] != null)
                {
                    dtRole = ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                //记录日志
                LogHelper.Error("获取所有角色-GetAllRoleList" + ex.ToString());
            }
            return dtRole;
        }

        /// <summary>
        /// 根据角色名称获取角色信息
        /// </summary>
        /// <param name="roleName">角色名称</param>
        /// <returns></returns>
        public DataTable GetRoleInfo(string roleName)
        {
            DataTable dtRole = new DataTable();

            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = "select * from Role where IsDelete=0 and RoleName=@RoleName";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@RoleName", DbType.String, roleName);
                var ds = db.ExecuteDataSet(Procdbcomm);
                if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count != 0)
                {
                    dtRole = ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                //记录日志
                LogHelper.Error("根据角色名称获取角色信息-GetRoleInfo" + ex.ToString());
            }
            return dtRole;
        }

        /// <summary>
        /// 根据角色编号获取角色菜单项信息
        /// </summary>
        /// <param name="roleID"></param>
        /// <returns></returns>
        public DataTable GetRoleMenuList(int roleID)
        {
            DataTable dt = new DataTable("RoleMenu");
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = @"select Id,rm.RoleID,rm.MenuID from RoleMenu as rm left join Menu as m on rm.MenuID = m.MenuID 
                              where rm.RoleID=@RoleID ";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@RoleID", DbType.Int32, roleID);
                var ds = db.ExecuteDataSet(Procdbcomm);
                if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count != 0)
                {
                    dt = ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                //记录日志
                LogHelper.Error("根据角色编号获取角色菜单项信息-GetRoleMenuList" + ex.ToString());
            }
            return dt;
        }

        /// <summary>
        /// 根据角色编号获取角色功能项信息
        /// </summary>
        /// <param name="roleID"></param>
        /// <returns></returns>
        public DataTable GetRoleActionList(int roleID)
        {
            DataTable dt = new DataTable("RoleAction");
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = @"select Id,ra.RoleID,ra.ActionID from RoleAction as ra left join Action as a on ra.ActionID = a.ActionID 
                                    where ra.RoleID=@RoleID ";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@RoleID", DbType.Int32, roleID);
                var ds = db.ExecuteDataSet(Procdbcomm);
                if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count != 0)
                {
                    dt = ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                //记录日志
                LogHelper.Error("根据角色编号获取角色功能项信息-GetRoleActionList" + ex.ToString());
            }
            return dt;
        }

        /// <summary>
        /// 删除角色菜单项
        /// </summary>
        /// <param name="roleID">角色编号</param>
        /// <returns></returns>
        public int DeleteRoleMenu(int roleID)
        {
            int ret = 0;
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = "delete from RoleMenu where RoleID=@RoleID";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@RoleID", DbType.Int32, roleID);
                ret = db.ExecuteNonQuery(Procdbcomm);
            }
            catch (Exception ex)
            {
                //记录日志
                ret = -99;
                LogHelper.Error("删除角色菜单项-DeleteRoleMenu" + ex.ToString());
            }
            return ret;
        }

        /// <summary>
        /// 删除角色功能项
        /// </summary>
        /// <param name="roleID">角色编号</param>
        /// <returns></returns>
        public int DeleteRoleAction(int roleID)
        {
            int ret = 0;
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = "delete from RoleAction where RoleID=@RoleID";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@RoleID", DbType.Int32, roleID);
                ret = db.ExecuteNonQuery(Procdbcomm);
            }
            catch (Exception ex)
            {
                //记录日志
                ret = -99;
                LogHelper.Error("删除角色功能项-DeleteRoleAction" + ex.ToString());
            }
            return ret;
        }

        /// <summary>
        /// 设置角色功能项
        /// </summary>
        /// <param name="roleID">角色编号</param>
        /// <param name="actionIds">功能id集合</param>
        /// <returns></returns>
        public int SetRoleAction(int roleID, string[] actionIds)
        {
            int ret = 0;

            try
            {
                DBHelper db = DBFactory.AdminDB;

                StringBuilder str = new StringBuilder();
                str.Append("insert into RoleAction (RoleID,ActionID) values ");
                for (int i = 0; i < actionIds.Length; i++)
                {
                    str.Append("(@RoleID,@ActionID").Append(i.ToString()).Append("),");
                }
                //去掉字符串最后一个,逗号
                string strSql = str.ToString().Substring(0, str.Length - 1);
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                for (int i = 0; i < actionIds.Length; i++)
                {
                    db.AddInParameter(Procdbcomm, "@ActionID" + i, DbType.Int64, actionIds[i]);
                }
                db.AddInParameter(Procdbcomm, "@RoleID", DbType.Int32, roleID);
                ret = db.ExecuteNonQuery(Procdbcomm);
            }
            catch (Exception ex)
            {
                //记录日志
                ret = -99;
                LogHelper.Error("设置角色功能项-SetRoleAction" + ex.ToString());
            }
            return ret;
        }

        /// <summary>
        /// 设置角色菜单项
        /// </summary>
        /// <param name="roleID">角色编号</param>
        /// <param name="menuIds">菜单id集合</param>
        /// <returns></returns>
        public int SetRoleMenu(int roleID, string[] menuIds)
        {
            int ret = 0;

            try
            {
                DBHelper db = DBFactory.AdminDB;

                StringBuilder str = new StringBuilder();
                str.Append("insert into RoleMenu (RoleID,MenuID) values ");
                for (int i = 0; i < menuIds.Length; i++)
                {
                    str.Append("(@RoleID,@MenuID").Append(i.ToString()).Append("),");
                }
                //去掉字符串最后一个,逗号
                string strSql = str.ToString().Substring(0, str.Length - 1);
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                for (int i = 0; i < menuIds.Length; i++)
                {
                    db.AddInParameter(Procdbcomm, "@MenuID" + i, DbType.Int64, menuIds[i]);
                }
                db.AddInParameter(Procdbcomm, "@RoleID", DbType.Int32, roleID);
                ret = db.ExecuteNonQuery(Procdbcomm);
            }
            catch (Exception ex)
            {
                //记录日志
                ret = -99;
                LogHelper.Error("设置角色菜单项-SetRoleMenu" + ex.ToString());
            }
            return ret;
        }
    }
}
